const express = require("express");
const router = express.Router();
const moment = require("moment");
// const { connection } = require("../util/mysql");
const fs = require("fs");
const path = require("path");

var mysql = require("mysql");
const env = {
  host: "111.231.22.97",
  user: "wangs-web",
  password: "HsTjw82zF7f6fjWY",
  database: "wangs-web", //数据库名字
};

const filters = (e) => {
  let objs = {};
  for (let keys in e) {
    if (e[keys] != "" && e[keys] != undefined && e[keys] != null) {
      objs[keys] = e[keys];
    }
  }
  return Object.keys(objs);
};

const pushes = (target, req, res) => {
  let pageNo = Number(req.query["pageNo"]);
  let pageSize = Number(req.query["pageSize"]);
  let offset = (pageNo - 1) * pageSize;
  console.log(target, "target");
  //判断全部查询还是走子查询
  if (target.length === 2) {
    console.log("全部查询");
    let sql = "select * from userlist limit ?,?";
    let sqls = "select count(*) from userlist";
    let pools = mysql.createPool(env);
    pools.query(sql, [offset, pageSize], (err, result) => {
      if (err) throw err;
      pools.query(sqls, (err, resl) => {
        if (err) throw err;
        //返回数据
        res.send({
          code: 200,
          msg: "success",
          data: {
            data: result,
            total: resl[0]["count(*)"],
          },
        });
        console.log(resl, "resl");
      });
    });
  } else {
    console.log("子查询");
    let target = filters(req.query).filter((res) => {
      return res != "pageNo" && res != "pageSize";
    });
    let arrs = [];
    let arrs1 = [];
    target.forEach((ele) => {
      arrs.push(req.query[ele]);
      arrs1.push(req.query[ele]);
    });
    let sql = "select * from userlist where ";
    let sqls = "select count(*) from userlist where ";
    target.forEach((ele, ind) => {
      if (ind != target.length - 1) {
        sql = sql + `${ele} = ? and `;
        sqls = sqls + `${ele} = ? and `;
      } else {
        sql = sql + `${ele} = ? limit ?,?`;
        sqls = sqls + `${ele} = ?`;
      }
    });
    arrs.push(offset, pageSize);
    let pools = mysql.createPool(env);
    pools.query(sql, arrs, (err, result) => {
      if (err) throw err;
      pools.query(sqls, arrs1, (err, resl) => {
        if (err) throw err;
        //返回数据
        res.send({
          code: 200,
          msg: "success",
          data: {
            data: result,
            total: resl[0]["count(*)"],
          },
        });
      });
    });
  }
};

router.get("/userlist", (req, res) => {
  const target = filters(req.query);
  pushes(target, req, res);
});

//新增id
const indexs = () => {
  let str = "";
  for (let i = 0; i < 10; i++) {
    let ind = Math.floor(Math.random() * 10);
    str += ind + 1;
  }
  return str;
};

//新增
const adds = (req, res) => {
  console.log(req.body);
  let sql = "insert into userlist values(?,?,?,?,?,?)";
  let pools = mysql.createPool(env);
  let id = indexs();
  pools.query(
    sql,
    [
      req.body.username,
      req.body.password,
      id,
      req.body.role,
      req.body.name,
      req.body.phone,
    ],
    (err, result) => {
      if (err) throw err;
      if (result.affectedRows == 1) {
        res.send({
          code: 200,
          msg: "success",
        });
      } else {
        res.send({
          code: 500,
          msg: "error",
        });
      }
    }
  );
};

//新增
router.post("/postUser", (req, res) => {
  adds(req, res);
});

//删除
const deletes = (req, res) => {
  let sql = "delete from userlist where id=?";
  let pools = mysql.createPool(env);
  pools.query(sql, [req.params.id], (err, result) => {
    if (err) throw err;
    if (result.affectedRows == 1) {
      res.send({
        code: 200,
        msg: "success",
      });
    } else {
      res.send({
        code: 500,
        msg: "error",
      });
    }
  });
};

//删除km
router.get("/deleteUser/:id", (req, res) => {
  deletes(req, res);
});

//修改成员信息
router.post("/updateUser", (req, res) => {
  let pools = mysql.createPool(env);
  let sql =
    "update userlist set username=?,password=?,role=?,name=?,phone=? where id=?";
  const { username, password, role, name, phone, id } = req.body;
  const arr = [username, password, role, name, phone, id];
  console.log(arr);
  pools.query(sql, arr, (err, result) => {
    if (err) throw err;
    if (result.affectedRows == 1) {
      res.send({
        code: 200,
        msg: "success",
      });
    } else {
      res.send({
        code: 500,
        msg: "error",
      });
    }
  });
});

const trees = (e, fid) => {
  let arrs = [];
  e.forEach((els) => {
    if (els.fdept == fid) {
      els.id = els.dept;
      els.label = els.deptName;
      els.children = [];
      els.children.push(...trees(e, els.dept));
      arrs.push(els);
    }
  });
  return arrs;
};

//获取组织架构
router.get("/getCom", (req, res) => {
  let pools = mysql.createPool(env);
  pools.getConnection(function (err, connection) {
    if (err) throw err;
    connection.query("select * from companylist", (err, result) => {
      if (err) {
        res.status(500);
        res.send({
          code: 500,
          msg: "error",
        });
      } else {
        res.send({
          code: 200,
          msg: "success",
          data: trees(result, null),
        });
      }
    });
    connection.release();
  });
});

router.get("/edit/:id", (req, res) => {
  editQuery(req, res, "select * from userlist where id=?");
});

//修改个人密码
const updatePassword = (req, res) => {
  let sql = "update userlist set password=? where username=?";
  let pools = mysql.createPool(env);
  pools.query(sql, [req.body.password, req.body.username], (err, result) => {
    if (err) throw err;
    if (result.affectedRows == 1) {
      res.send({
        code: 200,
        msg: "success",
      });
      return;
    }
    res.send({
      code: 500,
      msg: "error",
    });
  });
};
router.post("/updatePassword", (req, res) => {
  updatePassword(req, res);
});




//moblie端获取userlist信息(不改变pc端逻辑)

const moblieUserlist=(req,res)=>{
  let pools=mysql.createPool(env)
  if(req.body.key){
    let keys=req.body.key
    if(req.body.key=='管理员'){
      keys='1'
    }
    else if(req.body.key=='普通成员'){
      keys='0'
    }
    let arrs=['username','name','role'] //查询三次(包括账号、姓名、身份)
    let sql='select * from userlist where '
    let queryArr=[]
    arrs.forEach((el,index)=>{
      if(index!=arrs.length-1){
        sql+=`${el} = ? or `
        queryArr.push(keys)
      }
      else{
        sql+=`${el} = ?`
        queryArr.push(keys)
      }
    })
    pools.query(sql,queryArr,(err,resl)=>{
      if(err){
        res.status(500)
        console.log(err,'error')
      }
      else{
        res.send({
          code:200,
          msg:'success',
          data:{
            data:resl
          }
        })
      }
    })
  }
  else{
    let sql='select * from userlist'
    pools.query(sql,[],(err,resp)=>{
      if(err){
        res.status(500)
        console.log(err,'error')
      }
      res.send({
        code:200,
        msg:'success',
        data:{
          data:resp
        }
      })
    })
  }
}

router.post('/moblieUserlist',(req,res)=>{
    moblieUserlist(req,res)
})

module.exports = router;
